import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import javax.servlet.http.HttpServletRequest;

import xframe.util.UtilMaster;
import xframe.util.XMLQueryException;
import xframe.util.db.Row;


public class DataConversion {
	static String tb = "code";
	public static void main(String args[]){
		
		DataConversion dc = new DataConversion();

			ArrayList sourceList;
			try {
				sourceList = dc.doIt();
				dc.insetData(sourceList);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			
			
			

		
	}
	
	void insetData(ArrayList LIST){
	        PreparedStatement psmt = null;
	        Connection conB = getB();
	        Statement stmt = null;
	        
	        
	        ResultSet rs;
			try {
				conB.setAutoCommit(false);
				stmt = conB.createStatement();
				rs = stmt.executeQuery("select * from "+tb);

       	
	        ResultSetMetaData rsmt = rs.getMetaData();
	        
	        StringBuffer sb = new StringBuffer();
	        sb.append("insert into ");
	        sb.append(tb);
	        sb.append(" (");
	        int columsize = rsmt.getColumnCount();
	        for(int j = 1 ; j < columsize+1 ; j++){
	        	sb.append(rsmt.getColumnName(j));
	        	if(j < columsize){
	        		sb.append(", ");
	        	}
	        }
	        sb.append(" ) ");
	        sb.append("values");
	        sb.append(" (");
	        for(int j = 0 ; j < columsize ; j++){
	        	sb.append(" ?");
	        	if(j < columsize-1){
	        		sb.append(", ");
	        	}
	        }
	        sb.append(" ) ");
	        //System.out.println("Query : "+sb.toString());
	        
	        psmt = conB.prepareStatement(sb.toString());
	        
	        int loopSize = LIST.size();
	        Row row =	null;
	        for(int k=0; k < loopSize ; k++){
	        	row = new Row();
	        	row = (Row)LIST.get(k);
	        	//System.out.println("columsize: "+columsize);
	            //for(int i = 1; i < columsize+1; i++){
	            	 //System.out.println(i+": "+rsmt.getColumnName(i)+"\t"+row.get(rsmt.getColumnName(i)));
	           // }
	            
	            for(int i = 1; i < columsize+1; i++){
	            	 //System.out.println(i+": "+rsmt.getColumnName(i)+"\t"+row.get(rsmt.getColumnName(i)));
	                psmt.setObject(i, row.get(rsmt.getColumnName(i)));
	                
	            }
	            int returnValue = psmt.executeUpdate();
	            System.out.println((k+1)+"/"+loopSize+" 번째 실행");
			}
	        conB.commit();
	        stmt.close();
	        psmt.close();
	        conB.close();
	        System.out.println(loopSize+" 건 수 이관 완료");
			} catch (SQLException e) {
				e.printStackTrace();
				try {
					conB.rollback();
			        stmt.close();
			        psmt.close();
			        conB.close();					
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				
			}
	    }
		

	
	 ArrayList doIt() throws SQLException{
		
		Statement stmt = null;
        ResultSet rs = null;
        ArrayList LIST = null;
        Row columnIndexRow = new Row();
        Connection conA = getA();
        stmt = conA.createStatement();
        rs = stmt.executeQuery("Select * from "+tb);
        
        	
	        ResultSetMetaData rsmt = rs.getMetaData();
	        int columsize = rsmt.getColumnCount();
	        Row row = null;
	        
	        
	        String  encordingTo ="8859_1";
	        String  encordingFrom ="KSC5601";
	        String encordingStr = "";
	        boolean encoding = true;
	    	
	    	String param_string ="";
	        
	        LIST = new ArrayList();
	        for(; rs.next(); LIST.add(row)){
	        	row = new Row();
	            for(int i = 1; i < columsize + 1; i++){
	            	param_string = rs.getString(rsmt.getColumnLabel(i));
	            	if(encoding){
		            	if(param_string != null){
		    				try {
		    					//charSet4(param_string);
								encordingStr = new String(param_string.getBytes(encordingFrom),encordingTo);
							} catch (UnsupportedEncodingException e) {
								e.printStackTrace();
							}
		    			}else{
		    				encordingStr = null;
		    			}
	            	}else{
	            		encordingStr = param_string;
	            	}
	          row.put(rsmt.getColumnLabel(i), encordingStr);
	         // System.out.println("["+rsmt.getColumnLabel(i)+"]"+encordingStr);
	        }
	        
	    }
	        System.out.println(LIST.size()+" 건의 원본 데이터를 옮긴다.");
	        stmt.close();
	        conA.close();
	        
	        return LIST;
	            
	}
	
	

	
	Connection getA(){
        String url = "jdbc:mysql://localhost:3306/recipedb";
        String user = "root";
        String pass = "1111";
        Connection con= null;
        try {
			Class.forName("com.mysql.jdbc.Driver");
			con=DriverManager.getConnection(url,user,pass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
        
        return con;
	}
	
	Connection getB(){
		String url = "jdbc:mysql://localhost:3306/dumy";
		String user = "root";
		String pass = "1111";
		Connection con= null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con=DriverManager.getConnection(url,user,pass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return con;
	}
}
